package mahi.jdbc;
import java.util.*;
import java.sql.*;


public class PreparedStmtExample {
	
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	 static final String DB_URL = "jdbc:mysql://localhost/test";

	 //  Database credentials
	 static final String USER = "root";//"username";
	 static final String PASS = "";//"password";
	 
	 
	 public static void main(String[] args) {
		 HashMap<String, Integer> salesForWeek = new HashMap<>();
		 salesForWeek.put("a", 1);
		 salesForWeek.put("b", 2);
		Set<String> set  = salesForWeek.keySet();
		Collection<Integer> lst = salesForWeek. values();
		Set<Map.Entry<String, Integer>> e  = salesForWeek.entrySet();
	 }
	 
	public void updateCoffeeSales(HashMap<String, Integer> salesForWeek)
		    throws SQLException {

		    PreparedStatement updateSales = null;
		    PreparedStatement updateTotal = null;

		    String updateString =
		        "update COFFEES set SALES = ? where COF_NAME = ?";

		    String updateStatement =
		        "update COFFEES set TOTAL = TOTAL + ? where COF_NAME = ?";

		    Connection con= getConnection();
			try {
		        con.setAutoCommit(false);
		        
		        updateSales = con.prepareStatement(updateString);
		        updateTotal = con.prepareStatement(updateStatement);
	
		        for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
		            updateSales.setInt(1, e.getValue().intValue());  //5
		            updateSales.setString(2, e.getKey());//mahender
		            updateSales.executeUpdate();
		            updateTotal.setInt(1, e.getValue().intValue());
		            updateTotal.setString(2, e.getKey());
		            updateTotal.executeUpdate();
		            con.commit();
		        }
		    } catch (SQLException e ) {
		        e.printStackTrace();
		        if (con != null) {
		            try {
		                System.err.print("Transaction is being rolled back");
		                con.rollback();
		            } catch(SQLException excep) {
		                e.printStackTrace();
		            }
		        }
		    } finally {
		        if (updateSales != null) {
		            updateSales.close();
		        }
		        if (updateTotal != null) {
		            updateTotal.close();
		        }
		        con.setAutoCommit(true);
		    }
		}

	private Connection getConnection() {
		Connection conn = null;
		 try{
		    //STEP 2: Register JDBC driver
		    Class.forName("com.mysql.jdbc.Driver");

		    //STEP 3: Open a connection
		    System.out.println("Connecting to database...");
		    conn = DriverManager.getConnection(DB_URL,USER,PASS);
		 }catch(Exception ex)
		 {
			 ex.printStackTrace();
		 }
		return conn;
	}

}
